package d1023;
import java.sql.*;
import java.util.Scanner;
@SuppressWarnings("all")
public class EmpManager {
    static Scanner sc =new Scanner(System.in);

    public static void main(String[] args){

        EmpManager em = new EmpManager();
        while (true){
            System.out.println("*********************");
            System.out.println("*       1.查询      *");
            System.out.println("*       2.新增      *");
            System.out.println("*       3,修改      *");
            System.out.println("*       4.删改      *");
            System.out.println("*       0.退出      *");
            System.out.println("*********************");
            System.out.println("请输入操作编号：");
            String input = sc.nextLine();
            switch(input) {
                case "1":
                    em.query();
                    break;
                case "2":
                    em.add();
                case "3":
                    em.update();
                    break;
                case "4":
                    em.delete();
                    break;
                case "0":
                    System.out.println("谢谢使用");
                    return;
            }
        }
    }
    public void add(){
        try(Connection conn = getConnection()) {
            System.out.println("请输入新员工编号：");
            String empno = sc.nextLine();
            System.out.println("请输入新员工姓名：");
            String ename = sc.nextLine();
            System.out.println("请输入新员工入职时间(格式yyyy-mm-dd):");
            String s = sc.nextLine();
            Date date =Date.valueOf(s);
            System.out.println("请输入新员工领导：");
            String mgr = sc.nextLine();
            //验证mgr 是否合法
            String sql = "insert into emp (empno,ename,hiredate,mgr)"+"values(?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,empno);
            ps.setString(2,ename);
            ps.setDate(3,date);
            ps.setString(4,mgr);
            ps.executeUpdate();
            System.out.println("新员工添加成功!");
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
    public void query(){
        try(Connection conn = getConnection();){
            String sql = "select a.*,b.ename mgrname from emp a join emp b on a.mgr = b.empno";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            System.out.printf("%-10s %-10s %-10s %-10s  \n",
                    "编号",
                    "姓名",
                    "入职时间",
                    "领导");
            while(rs.next()){
                System.out.printf("%-10s %-10s %-10s %-10s  \n",
                        rs.getString("EMPNO"),
                        rs.getString("ENAME"),
                        rs.getDate("HIREDATE"),
                        rs.getString("MGRNAME"));
            }
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }

    public void update(){
        try(Connection conn = getConnection()){
            System.out.println("请输入要修改员工编号:");
            String empno = sc.nextLine();
            System.out.println("请输入要修改的员工姓名");
            String ename = sc.nextLine();
            System.out.println("请输入新员工入职时间(格式yyyy-mm-dd):");
            String s = sc.nextLine();
            Date date = Date.valueOf(s);
            System.out.println("请输入要修改的领导:");
            String mgr = sc.nextLine();

            String ename1="update emp set enmae=? where empno=?";
            String date1="update emp set hiredate=? where empno=?";
            String emgr1="update emp set mgr=? where empno =?";

            PreparedStatement p1 = conn.prepareStatement(ename1);
            PreparedStatement p2 = conn.prepareStatement(date1);
            PreparedStatement p3 = conn.prepareStatement(emgr1);

            p1.setString(1,ename);
            p1.setString(2,empno);
            p1.executeUpdate();

            p2.setString(1,date1);
            p2.setString(2,empno);
            p2.executeUpdate();

            p3.setString(1,mgr);
            p3.setString(2,empno);
            p3.executeUpdate();
            System.out.println("员工信息修改成功！");
        }catch (SQLException e){
            e.printStackTrace();
        }
    }


    public void delete(){
        try (Connection conn = getConnection()){
            String sql = "delete from emp where empno=?";
            String sql2 = "select empno from where mgr=?";
            PreparedStatement ps = conn.prepareStatement(sql2);
            System.out.println("请输入要删除的员工编号：");
            String empno = sc.nextLine();
            ps.setString(1,empno);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                System.out.println("该员工有下属。。");
            }else{
                ps = conn.prepareStatement(sql);
                ps.setString(1,empno);
                ps.executeUpdate();
                System.out.println("删除成功！");
            }

        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    public Connection getConnection(){
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
            return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","123");
        }catch(Exception e){
            throw  new RuntimeException(e);
        }
    }
    public static  void  close(ResultSet set, Statement stat,Connection conn){
        try{
            if (set!=null){
                set.close();
            }else if (stat!=null){
                stat.close();
            }else if(conn!=null){
                conn.close();
            }
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
    }


}

